﻿using AngleSharp.Dom;
using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Extensions;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.Entitys;
using RndMaterialDisposalService.Models.ResponseModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface;
using Masuit.Tools;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NPOI.SS.Formula.Functions;
using NPOI.Util;
using System.Linq;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace RndMaterialDisposalService.Services
{
    /// <summary>
    /// 
    /// </summary>
    [NAutowired(Lifecycle.Scoped)]
    public class PortalCommonService : IPortalCommonService
    {
        private readonly EFExtension efExtension;
        private readonly EDPPlatformContext dbEDPPlatformContext;
        private readonly K2Context k2Context;
        private readonly AssetsManagementContext assetsManagementContext;
        private readonly WorkflowCommonDataContext workflowCommonDataContext;
        private readonly IConfiguration configuration;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_efExtension"></param>
        /// <param name="_dbEDPPlatformContext"></param>
        /// <param name="_k2Context"></param>
        /// <param name="_assetsManagementContext"></param>
        /// <param name="_workflowCommonDataContext"></param>
        /// <param name="_configuration"></param>
        public PortalCommonService(EFExtension _efExtension, EDPPlatformContext _dbEDPPlatformContext,
            K2Context _k2Context, AssetsManagementContext _assetsManagementContext,
            WorkflowCommonDataContext _workflowCommonDataContext,
            IConfiguration _configuration)
        {
            efExtension = _efExtension;
            dbEDPPlatformContext = _dbEDPPlatformContext;
            k2Context = _k2Context;
            configuration = _configuration;
            assetsManagementContext = _assetsManagementContext;
            workflowCommonDataContext = _workflowCommonDataContext;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="keyWord"></param>
        /// <param name="sortField"></param>
        /// <param name="SortDir"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ToDoModel>, int)> GetTodoList(string userId, string? keyWord, string sortField = "ArrivalDate", string SortDir = "desc", int pageIndex = 1, int pageSize = 10)
        {


            string sql = $@"EXEC [dbo].[UP_MobileToDoList]
		                        @UserID = N'{userId}',
                                @Memo = N'{keyWord}'";
            var todoList = efExtension.SqlQuery<ToDoModel>(dbEDPPlatformContext, sql,new List<SqlParameter>());

            var processConfig = await MobileProcesslist();
            var processNames = processConfig.Where(p => "1".Equals(p.Field1)).Select(p => p.Name ?? "").ToList();
            var result = todoList?.Where(p => !string.IsNullOrEmpty(p.ModuleDescription) && processNames.Contains(p.ModuleDescription))
                .OrderByDescending(p => p.ArrivalDate).ToList();

            var index = 0;
            result?.ForEach(p =>
            {
                index += 1;
                p.RowNo = index;
            });

            var totalCount = result?.Count ?? 0;
            var data = result?.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return (data ?? new List<ToDoModel>(), totalCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="keyWord"></param>
        /// <param name="sortField"></param>
        /// <param name="SortDir"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<VToDoneDto>, int)> GetDoneList(string userId, string? keyWord, string sortField = "ArrivalDate", string SortDir = "desc", int pageIndex = 1, int pageSize = 10)
        {
            var processConfig = await MobileProcesslist();
            var processNames = processConfig.Where(p => p.Field6 == "1").Select(p => p.Name ?? "").ToList();
            var VToDoneList = dbEDPPlatformContext.VToDone.Where(p => processNames != null && processNames.Any() && processNames!.Contains(p.ModuleMemo));
            if (!string.IsNullOrEmpty(userId))
            {
                VToDoneList = VToDoneList.Where(p => p.UserID == userId);
            }
            if (!string.IsNullOrEmpty(keyWord))
            {
                VToDoneList = VToDoneList.Where(p => !string.IsNullOrEmpty(p.Memo) && p.Memo.Contains(keyWord));
            }
            var totalCount = await VToDoneList.CountAsync();
            var data = await VToDoneList.OrderByDescending(p => p.ArrivalDate).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

            var index = 1;
            data.ForEach(p =>
            {
                var rowNo = (pageIndex - 1) * pageSize + index;
                p.RowNo = rowNo;
                index += 1;
            });

            return (data, totalCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="keyWord"></param>
        /// <param name="sortField"></param>
        /// <param name="SortDir"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<VToReadDto>, int)> GetToReadList(string userId, string? keyWord, string sortField = "ArrivalDate", string SortDir = "desc", int pageIndex = 1, int pageSize = 10)
        {
            var processConfig = await MobileProcesslist();
            var processNames = processConfig.Where(p => p.Field5 == "1").Select(p => p.Name ?? "").ToList();
            var VToReadList = dbEDPPlatformContext.VToRead.Where(p => processNames != null && processNames.Any() && processNames!.Contains(p.ModuleMemo));
            if (!string.IsNullOrEmpty(userId))
            {
                VToReadList = VToReadList.Where(p => p.UserID == userId);
            }
            if (!string.IsNullOrEmpty(keyWord))
            {
                VToReadList = VToReadList.Where(p => !string.IsNullOrEmpty(p.Memo) && p.Memo.Contains(keyWord));
            }
            var totalCount = await VToReadList.CountAsync();
            var data = await VToReadList.OrderByDescending(p => p.ArrivalDate).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

            var index = 1;
            data.ForEach(p =>
            {
                var rowNo = (pageIndex - 1) * pageSize + index;
                p.RowNo = rowNo;
                index += 1;
            });

            return (data, totalCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="procInstID"></param>
        /// <returns></returns>
        public async Task<List<WorkItemUser>> GetWorkListItemUserByProcInstID(int procInstID)
        {
            string sql = $@"EXEC [dbo].[UP_WorkListItem_GetListByProcInstID]
		                        @ProcInstID = {procInstID}";
            var result = efExtension.SqlQuery<WorkItemUser>(k2Context, sql,new List<SqlParameter>());
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="procSetId"></param>
        /// <param name="folio"></param>
        /// <param name="originator"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ProcInst>, int)> GetProcInst(int procSetId, string folio, string originator, DateTime? startDate, DateTime? endDate, int pageIndex = 1, int pageSize = 10)
        {
            var startIndex = (pageIndex - 1) * pageSize + 1;
            var endIndex = startIndex + pageSize - 1;
            string sql = $@"EXEC [dbo].[UP_ProcInst_GetModelListByPage]
		                        @ProcID = {procSetId},
                                @Folio = N'{folio}',
                                @Originator = N'{originator}',
                                @StartDate = N'{startDate}',
                                @EndDate = N'{endDate}',
                                @StartIndex = {startIndex},
                                @EndIndex = {endIndex}";
            var result = efExtension.SqlQuery<ProcInst>(k2Context, sql, new List<SqlParameter>());

            var totalCount = result != null && result.Any() ? result[0].RowCnt : 0;
            var data = result;
            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<(int todoCount, int toReadCount)> GetTodoToReadCount(string userId)
        {
            //待办数量
            string sql = $@"EXEC [dbo].[UP_UserToDoCount] @UserID = N'{userId}'";
            var processConfig = await MobileProcesslist();
            var processNames = processConfig.Where(p => "1".Equals(p.Field1)).Select(p => p.Name ?? "").ToList();
            var todoList = efExtension.SqlQuery<TodoAndToReadCountResp>(dbEDPPlatformContext, sql, new List<SqlParameter>());
            var todoCount = todoList?.Where(p => !string.IsNullOrEmpty(p.ModuleDescription) && processNames.Contains(p.ModuleDescription)).Count() ?? 0;

            //待阅数量
            var toReadProcessNames = processConfig.Where(p => "1".Equals(p.Field5)).Select(p => p.Name ?? "").ToList();
            var VToReadList = dbEDPPlatformContext.VToRead
                .Where(p => p.UserID == userId && p.Status == 3 && toReadProcessNames != null && toReadProcessNames.Any() && toReadProcessNames!.Contains(p.ModuleMemo));
            var toReadCount = await VToReadList.CountAsync();

            return (todoCount, toReadCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <exception cref=""></exception>
        public async Task<List<StandardDictionaryConfigurationModel>> MobileProcesslist()
        {
            var result = from dict1 in assetsManagementContext.StandardDictionaryConfiguration
                         join dict2 in assetsManagementContext.StandardDictionaryConfiguration on dict1.ParentId equals dict2.Id
                         where dict2.Code == "MobileStartProcessList"
                             && dict1.Type == 2
                             && dict1.IsDeleted == false
                         select dict1;

            return await result.ToListAsync();
        }


        /// <summary>
        /// 获取当前用户所有待办
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="keyWord"></param>
        /// <param name="sortField"></param>
        /// <param name="SortDir"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<BatchApprovalToDoModel> GetPCTodoList(string userId, string? keyWord, string sortField = "ArrivalDate", string SortDir = "desc", int pageIndex = 1, int pageSize = 10)
        {
            string sql = $@"EXEC [dbo].[UP_MobileToDoList]
		                        @UserID = N'{userId}',
                                @Memo = N'{keyWord}'";
            var todoList = efExtension.SqlQuery<ToDoModel>(dbEDPPlatformContext, sql, new List<SqlParameter>());

            var result = todoList.OrderByDescending(p => p.ArrivalDate).ToList();

            var index = 0;
            result?.ForEach(p =>
            {
                index += 1;
                p.RowNo = index;
            });

            List<BatchApprovalToDoModel> BatchApprovalToDoList = new List<BatchApprovalToDoModel>();
            if (result.Any())
            {
                List<int> procInstIdList = result.Select(a => a.FunctionInstanceID).ToList();
                string _sql = $"SELECT ApplicationNumber,ProcInstId,FormData,MailBody,ApplicantEmployeeNumber,ApplicantName FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId IN({string.Join(",", procInstIdList)})";
                var list = SqlQuery<WorkflowApplicationFormData>(_sql, new List<SqlParameter>());
                list.ForEach(async (x) =>
                {

                    int _procInstId = Convert.ToInt32(x.ProcInstId);
                    var item = result.FirstOrDefault(a => a.FunctionInstanceID == _procInstId);
                    List<WorkItemUser> workItemUserList = await GetWorkListItemUserByProcInstID(_procInstId);
                    if (item != null)
                    {
                        BatchApprovalToDoList.Add(new BatchApprovalToDoModel
                        {
                            ApplicationNumber = x.ApplicationNumber.Trim(),
                            FormData = x.FormData,
                            MailBody = x.MailBody,
                            ApplicantName = $"{x.ApplicantEmployeeNumber.Trim()} {x.ApplicantName.Trim()}",
                            RowCnt = item.RowCnt,
                            RowNo = item.RowNo,
                            FunctionID = item.FunctionID,
                            FunctionInstanceID = item.FunctionInstanceID,
                            FunctionInstanceUrl = item.FunctionInstanceUrl,
                            UserID = item.UserID,
                            StartDate = item.StartDate,
                            ArrivalDate = item.ArrivalDate,
                            Memo = item.Memo,
                            FunctionDescription = item.FunctionDescription,
                            ApplicationName = item.ApplicationName,
                            Originator = item.Originator,
                            ScopeID = item.ScopeID,
                            ModuleId = item.ModuleId,
                            ModulePath = item.ModulePath,
                            ModuleName = item.ModuleName,
                            ModuleDescription = item.ModuleDescription,
                            ActID = workItemUserList[0].ActID,
                            ActionerName = workItemUserList[0].ActionerName,
                            TaskName = workItemUserList[0].name,
                            Sn = workItemUserList[0].SN,
                        });
                    }
                });
            }


            var data = result?.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return (BatchApprovalToDoList ?? new List<BatchApprovalToDoModel>());
        }

        /// <summary>
        /// 根据批量流程实例号查询实例
        /// </summary>
        /// <param name="procInstIdList"></param>
        /// <returns></returns>
        public async Task<List<ApplicationDataModel>> GetBatchProcessInstance(List<string> procInstIdList)
        {
            var item = await (from p in workflowCommonDataContext.ApplicationData
                              where (procInstIdList).Contains(p.ProcInstId)
                              select p).ToListAsync();
            return item;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = assetsManagementContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) =>
                    {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="moduleIds"></param>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<ToDoModel> Data, int TotalCount)> GetEDPWorkItemList(string userId, string keyword, int pageIndex, int pageSize)
        {
            string sql = $@"EXEC [dbo].[UP_MobileToDoList] @UserID = N'{userId}', @Memo = N'{keyword}'";
            var todoList = efExtension.SqlQuery<ToDoModel>(dbEDPPlatformContext, sql, new List<SqlParameter>()) ?? new List<ToDoModel>();

            var appName = configuration.GetSection("ApplicationName").Value ?? "";
            var result = todoList.WhereIf(!string.IsNullOrEmpty(appName), p => appName.Equals(p.ApplicationName))
                .WhereIf(!string.IsNullOrEmpty(keyword), p => !string.IsNullOrEmpty(p.Memo) && p.Memo.Contains(keyword))
                .OrderByDescending(p => p.ArrivalDate);
            var totalCount = result.Count();
            var data = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="moduleIds"></param>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<VToDoneDto> Data, int TotalCount)> GetEDPWorkDoneList(string userId, string keyword, int pageIndex, int pageSize)
        {
            var appName = configuration.GetSection("ApplicationName").Value ?? "";
            var VToDoneList = dbEDPPlatformContext.VToDone.WhereIf(!string.IsNullOrEmpty(appName), p => appName.Equals(p.ApplicationName))
                .WhereIf(!string.IsNullOrEmpty(userId), p => p.UserID == userId)
                .WhereIf(!string.IsNullOrEmpty(keyword), p => !string.IsNullOrEmpty(p.Memo) && p.Memo.Contains(keyword));

            var totalCount = await VToDoneList.CountAsync();
            var data = await VToDoneList.OrderByDescending(p => p.ArrivalDate).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

            return (data, totalCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="moduleIds"></param>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(List<VToReadDto> Data, int TotalCount)> GetEDPWorkReadList(string userId, string keyword, int pageIndex, int pageSize)
        {
            var appName = configuration.GetSection("ApplicationName").Value ?? "";
            var VToReadList = dbEDPPlatformContext.VToRead.WhereIf(!string.IsNullOrEmpty(appName), p => appName.Equals(p.ApplicationName))
                .WhereIf(!string.IsNullOrEmpty(userId), p => p.UserID == userId)
                .WhereIf(!string.IsNullOrEmpty(keyword), p => !string.IsNullOrEmpty(p.Memo) && p.Memo.Contains(keyword));

            var totalCount = await VToReadList.CountAsync();
            var data = await VToReadList.OrderByDescending(p => p.ArrivalDate).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

            return (data, totalCount);
        }
    }
}
